package com.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.text.SimpleDateFormat;

import com.entity.Apply;
import com.entity.User;
import com.service.CalculateSalary;
import com.util.DBUtil;

public class OrdinaryEmployeesDao {

	public static int count = 0;
	//获取个人信息
	public static ArrayList<User> getUserInfo(String userid,String condition,String conditionValue,boolean all) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		ArrayList<User> userlist = new ArrayList<User>();
		User user = null;
		String sql = null;
		if(condition == null) {
			condition = "";
		}
		try {
			//判断是个人查询还是 全部查询
			if(all) {
				switch(condition) {
				case "deptname":
					sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and deptname like ?;";
					
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, '%'+conditionValue+'%');
					break;
				case "rolename":
					sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and rolename like ?;";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setString(1, '%'+conditionValue+'%');
					break;
//				case "status":
//					sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and status like ?;";
//					pst = DBUtil.getConnection().prepareStatement(sql);
//					pst.setString(1, '%'+conditionValue+'%');
//					break;
				default :
					sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid;";
					pst = DBUtil.getConnection().prepareStatement(sql);
					break;
				}
			}else {
				sql = "select * from user,deptrole where userid=? and user.deptid=deptrole.deptid and user.roleid=deptrole.roleid;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, userid);
			}
			System.out.println("sql="+pst);
			rs = pst.executeQuery();
			while(rs.next()) {
				user = new User();
				user.setUserid(rs.getString("Userid"));
				user.setDeptid(rs.getString("Deptid"));
				user.setDeptname(rs.getString("Deptname"));
				user.setRoleid(rs.getString("Roleid"));
				user.setRolename(rs.getString("Rolename"));
				user.setStatus(rs.getString("Status"));
				user.setName(rs.getString("Name"));
				user.setSex(rs.getString("Sex"));
				user.setAge(rs.getInt("Age"));
				user.setPhone(rs.getString("Phone"));
				user.setCreatetime(rs.getString("Create_time"));
				user.setBasewages(rs.getFloat("Basewages"));
				user.setMeritwages(rs.getFloat("Meritwages"));
				user.setPunishingwages(rs.getFloat("Punishingwages"));
				user.setTotalwages(rs.getFloat("Totalwages"));
				user.setOvertime(rs.getInt("Overtime"));
				user.setWeekovertime(rs.getInt("Weekovertime"));
				user.setFestovertime(rs.getInt("Festovertime"));
				user.setOvertimestandard(rs.getFloat("Overtimestandard"));
				user.setCommission(rs.getFloat("Commission"));
				user.setFestivalwages(rs.getFloat("Festivalwages"));
				user.setYearendwages(rs.getFloat("Yearendwages"));
				user.setLeavecount(rs.getInt("Leavecount"));
				user.setAbsentcount(rs.getInt("Absentcount"));
				user.setLeavestandard(rs.getFloat("Leavestandard"));
				user.setAbsentstandard(rs.getFloat("Absentstandard"));
				user.setIspay(rs.getString("Ispay"));
				user = CalculateSalary.calculate(user);
				userlist.add(user);
			//	剩余权限数据...
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
//		System.out.println(userlist.size());
		return userlist;
	}
	//修改个人信息
	public static boolean updateUserInfo(String userid,String field,Object newinfo) {
		boolean flag = false;
		PreparedStatement pst = null;
		String sql = null;
		switch (field) {
		case "dept":
			sql = "update user set deptid=? where userid=?";
			break;
		case "role":
			sql = "update user set roleid=? where userid=?";
			break;
		case "pwd":
			sql = "update user set Pwd=? where userid=?";
			break;
		case "status":
			sql = "update user set status=? where userid=?";
			break;
		case "name":
			sql = "update user set Name=? where userid=?";
			break;
		case "sex":
			sql = "update user set Sex=? where userid=?";
			break;
		case "age":
			sql = "update user set Age=? where userid=?";
			break;
		case "phone":
			sql = "update user set Phone=? where userid=?";
			break;
		case "basewages":
			sql = "update user set basewages=? where userid=?";
			break;
		case "overtimestandard":
			sql = "update user set overtimestandard=? where userid=?";
			break;
		case "leavestandard":
			sql = "update user set leavestandard=? where userid=?";
			break;
		case "absentstandard":
			sql = "update user set absentstandard=? where userid=?";
			break;
		case "totalwages":
			sql = "update user set totalwages=? where userid=?";
			break;
		case "meritwages":
			sql = "update user set meritwages=? where userid=?";
			break;
		case "punishingwages":
			sql = "update user set Punishingwages=? where userid=?";
			break;
		}
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setObject(1, newinfo);
			pst.setString(2, userid);
			flag = (pst.executeUpdate()!=0);
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	//加班录入
	public static boolean inputOvertime(String userid,int overtime,int weekovertime,int festovertime,String applydescribe) {
		boolean flag = false;
		PreparedStatement pst = null;
		String sql = null;
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
		String nowtime = df.format(new Date());// new Date()为获取当前系统时间
		sql = "insert into apply(applicant,applytime,`Status`,power,overtime,weekovertime,festovertime,Applydescribe,operater,applytype)values(?,?,'0','1',?,?,?,?,?,'inputOvertime')";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setString(1, userid);
			pst.setString(2, nowtime);
			pst.setInt(3, weekovertime);
			pst.setInt(4, overtime);
			pst.setInt(5, festovertime);
			pst.setString(6, applydescribe);
			pst.setString(7, userid);
			flag = (pst.executeUpdate()!=0);
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	//查阅申请的审核状态
	@SuppressWarnings("null")
	public static ArrayList<Apply> getSelfApply(String userid,int page,int limit){
		ArrayList<Apply> applylist = new ArrayList<Apply>();
		PreparedStatement pst = null;
		ResultSet rs = null;
		Apply apply = null;
		String sql = null;
		sql = "select * from apply where applicant=? limit ?,?";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setString(1, userid);
			pst.setInt(2, page);
			pst.setInt(3, limit);
			System.out.println("pst="+pst);	//测试 
			rs = pst.executeQuery();
			while(rs.next()) {
				apply = new Apply();
				apply.setApplyid(rs.getInt("Applyid"));
				apply.setApplicant(rs.getString("Applicant"));
				apply.setApplytime(rs.getString("Applytime"));
				apply.setStatus(rs.getString("Status"));
				apply.setPower(rs.getString("Power"));
				apply.setOperater(rs.getString("Operater"));
				apply.setHandle(rs.getString("Handle"));
				apply.setHandletime(rs.getString("Handletime"));
				apply.setOvertime(rs.getInt("Overtime"));
				apply.setWeekovertime(rs.getInt("Weekovertime"));
				apply.setFestovertime(rs.getInt("Festovertime"));
				apply.setLeavecount(rs.getInt("Leavecount"));
				apply.setAbsentcount(rs.getInt("Absentcount"));
				apply.setFestivalbonus(rs.getFloat("Festivalbonus"));
				apply.setYearbonus(rs.getFloat("Yearbonus"));
				apply.setCommission(rs.getFloat("Commission"));
				apply.setApplydescribe(rs.getString("Applydescribe"));
				apply.setApplytype(rs.getString("Applytype"));
				applylist.add(apply);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		//获取数据数
		sql = "select * from apply where applicant=?";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setString(1, userid);
			rs = pst.executeQuery();
			System.out.println("Ordinary pst="+pst);
			rs.last();
			count = rs.getRow();
			System.out.println("count:"+count);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return applylist;
	}
}
